阿里天池学习 |
您所在的位置：网站首页 › 7 4 集合减法 10 分 › 阿里天池学习 |
阿里天池学习
|
4.1表的加减法4.1.1 什么是集合运算 集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。 在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。 以下的文氏图展示了几种集合的基本运算.  [图片来源于网络] 4.1.2 表的加法–UNION4.1.2.1 UNION建表代码及数据导入请使用第一章提供的代码. 接下来我们演示UNION的具体用法及查询结果: SELECT product_id, product_name FROM product UNION SELECT product_id, product_name FROM product2;这其实相当于集合中的并集运算但是 UNION 等集合运算符通常都会除去重复的记录. 4.1.2.2 包含重复行的集合运算 UNION ALLSQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了. 4.1.2.3[扩展阅读]bag 模型与 set 模型Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素. 是否允许元素重复导致了 set 和 bag 的并交差等运算都存在一些区别. 以 bag 的交为例, 由于 bag 允许元素重复出现, 对于两个 bag, 他们的并运算会按照: 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数 这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}. 4.1.2.5隐式类型转换通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型: SELECT product_id, product_name, '1' FROM product UNION SELECT product_id, product_name,sale_price FROM product2;上述查询能够正确执行,得到如下结果:  4.1.3 差集,补集与表的减法求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。  4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法. 4.1.4.2 EXCEPT 与 NOT 谓词4.1.4.3 EXCEPT ALL 与bag 的差类似于UNION ALL, EXCEPT ALL 也是按出现次数进行减法, 也是使用bag模型进行运算. 对于两个 bag, 他们的差运算会按照: 1.该元素是否属于作为被减数的 bag, 2.该元素在两个 bag 中的出现次数 这两个方面来进行计算. 只有属于被减数的bag的元素才参与EXCEP ALL运算, 并且差bag中的次数,等于该元素在两个bag的出现次数之差(差为零或负数则不出现). 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}. 4.1.4.4 INTERSECT 与 AND 谓词对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现. 4.1.5对称差两个集合A,B的对称差：是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差. 4.2连结(JOIN)连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询. SQL 中的连结有多种分类方法, 我们这里使用最基础的内连结和外连结的分类方法来分别进行讲解. 4.2.1 内连结(INNER JOIN)内连结的语法格式是: -- 内连结 FROM INNER JOIN ON其中 INNER 关键词表示使用了内连结, 注:如果你使用过 excel 的 vlookup 函数, 你会发现这个函数正好也能够实现这个功能. 实际上, 在思路上, 关联子查询更像是 vlookup 函数: 以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行.当数据量较少时, 这种方式并不会有什么性能问题, 但数据量较大时, 这种方式将会导致较大的计算开销: 对于外部查询返回的每一行数据, 都会向内部的子查询传递一个关联列的值, 然后内部子查询根据传入的值执行一次查询然后返回它的查询结果. 这就使得, 例如外部主查询的返回结果有一万行, 那么子查询就会执行一万次, 这将会带来非常恐怖的时间消耗.关于内连结,需要注意以下三点: 要点一: 进行连结时需要在 FROM 子句中使用多张表. 之前的 FROM 子句中只有一张表, 而这次我们同时使用了shopproduct 和product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了: FROMshopproduct AS SP INNER JOIN product AS P要点二:必须使用 ON 子句来指定连结条件. 在进行内连结时 ON 子句是必不可少的，ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用, 我们会在本章的结尾部分进一步探讨这个话题. 要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用. 当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名 4.2.1.1 结合 WHERE 子句使用内连结如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边. 4.2.1.2结合 GROUP BY 子句使用内连结结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待. 最简单的情形, 是在内连结之前就使用 GROUP BY 子句. 但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合. 4.2.1.3自连结(SELF JOIN)之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法. 4.2.1.4自然连结(NATURAL JOIN)自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件. 4.2.2 外连结(OUTER JOIN)内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行. 按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结. 左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充. 三种外连结的对应语法分别为: -- 左连结 FROM LEFT OUTER JOIN ON -- 右连结 FROM RIGHT OUTER JOIN ON -- 全外连结 FROM FULL OUTER JOIN ON●外连结要点 1: 选取出单张表中全部的信息 ●****外连结要点 2:使用 LEFT、RIGHT 来指定主表. 4.2.3多表连结通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。 4.2.3.1 多表进行内连结SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price ,IP.inventory_quantity FROMshopproduct AS SP INNER JOINproduct AS P ON SP.product_id = P.product_id INNER JOIN Inventoryproduct AS IP ON SP.product_id = IP.product_id WHERE IP.inventory_id = 'P001';得到如下结果  我们可以看到, 连结第三张表的时候, 也是通过 ON 子句指定连结条件(这里使用最基础的等号将作为连结条件的product 表和shopproduct 表中的商品编号 product _id 连结了起来), 由于product 表和shopproduct 表已经进行了连结,因此就无需再对product 表和 Inventoryproduct 表进行连结了(虽然也可以进行连结,但结果并不会发生改变, 因为本质上并没有增加新的限制条件). 即使想要把连结的表增加到 4 张、5 张……使用 INNER JOIN 进行添加的方式也是完全相同的. 4.2.3.2多表进行外连结正如之前所学发现的, 外连结一般能比内连结有更多的行, 从而能够比内连结给出更多关于主表的信息, 多表连结的时候使用外连结也有同样的作用. 例如, SELECT P.product_id ,P.product_name ,P.sale_price ,SP.shop_id ,SP.shop_name ,IP.inventory_quantity FROMproduct AS P LEFT OUTER JOINshopproduct AS SP ON SP.product_id = P.product_id LEFT OUTER JOIN Inventoryproduct AS IP ON SP.product_id = IP.product_id4.2.4 ON 子句进阶–非等值连结在刚开始介绍连结的时候, 书上提到过, 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件. 4.2.4.1非等值自左连结(SELF JOIN)使用非等值自左连结实现排名。 4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处. 交叉连结的语法有如下几种形式: -- 1.使用关键字 CROSS JOIN 显式地进行交叉连结 SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM shopproduct AS SP CROSS JOIN product AS P; --2.使用逗号分隔两个表,并省略 ON 子句 SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM shopproduct AS SP ,product AS P;对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN （笛卡儿积）.进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。 4.2.5.1[扩展阅读]连结与笛卡儿积的关系考察笛卡儿积和连结, 不难发现, 笛卡儿积可以视作一种特殊的连结(事实上笛卡儿积的语法也可以写作 CROSS JOIN), 这种连结的 ON 子句是一个恒为真的谓词. 反过来思考, 在对笛卡儿积进行适当的限制之后, 也就得到了内连结和外连结. 对上述笛卡尔乘积增加筛选条件（where）同内连接on条件, 就得到了和内连结一致的结果: 资料来源：阿里天池：SQL训练营 |
【本文地址】